<?php


namespace app\admin\service\base;


use app\admin\model\base\Classes;
use app\admin\model\base\Grades;
use app\admin\model\base\Special;
use app\admin\model\base\Student;
use app\admin\model\base\StudentGroup;
use app\admin\validates\StudentValidate;
use app\BaseService;
use export\Export;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\exception\ValidateException;
use think\facade\Db;
use think\facade\Log;
use think\Model;
use think\response\Json;

class StudentService extends BaseService
{
    /**
     * 班级列表
     * @param array $params
     * @return Json
     */
    public function index(array $params): Json
    {
        $where[] = ['is_del','=',0];
        if(isset($params['special_id']) && $params['special_id']){
            $where[] = ['special_id','=',$params['special_id']];
        }
        if(isset($params['classes_id']) && $params['classes_id']){
            $where[] = ['classes_id','=',$params['classes_id']];
        }
        if(isset($params['grades_id']) && $params['grades_id']){
            $where[] = ['grades_id','=',$params['grades_id']];
        }
        if(isset($params['student_group_id']) && $params['student_group_id']){
            $where[] = ['student_group_id','like',"%{$params['student_group_id']},%"];
        }
        if(isset($params['realname']) && $params['realname']){
            $where[] = ['realname','like',"%{$params['realname']}%"];
        }
        $data = Student::getList($where, $params['page'] ?? 1, $params['limit'] ?? 10, 'id desc','username,realname,sex,special_id,id,login_ip,login_time,created_at,updated_at,number,grades_id,classes_id,student_group_id');
        return jsonReturn(1, $data);
    }

    /**
     * 导出学生列表
     * @return Json
     */
    public function indexExport(array $params):Json
    {
        $where = [];
        if(isset($params['special_id']) && $params['special_id']){
            $where[] = ['special_id','=',$params['special_id']];
        }
        if(isset($params['classes_id']) && $params['classes_id']){
            $where[] = ['classes_id','=',$params['classes_id']];
        }
        if(isset($params['grades_id']) && $params['grades_id']){
            $where[] = ['grades_id','=',$params['grades_id']];
        }
        if(isset($params['student_group_id']) && $params['student_group_id']){
            $where[] = ['student_group_id','like',"%{$params['student_group_id']},%"];
        }
        if(isset($params['realname']) && $params['realname']){
            $where[] = ['realname','like',"%{$params['realname']}%"];
        }
//        if(empty($where)) return jsonReturn(0, [],'请选择导出的条件');
        $where[] = ['is_del','=',0];
        $list = Student::where($where)->field('id,username,sex,grades_id,classes_id,special_id,realname')->select()->toArray();
        //查询所有
        $grades = Grades::select()->toArray();
        $grades_ids = array_column($grades,'name','id');
        $special = Special::select()->toArray();
        $special_ids = array_column($special,'name','id');
        $classes = Classes::select()->toArray();
        $classes_ids = array_column($classes,'name','id');
        $arr = [];
        $sex_list = ['1'=>'男','2'=>'女'];
        $arrName = ['姓名','学号','性别','专业','年级','班级'];
        if(!empty($list)){
            foreach ($list as $value){
                //组装导出数据
                $arr[] = [
                    $value['realname'],
                    $value['username'],
                    $sex_list[$value['sex']] ?? '未知',
                    $special_ids[$value['special_id']] ?? '暂无',
                    $grades_ids[$value['grades_id']] ?? '暂无',
                    $classes_ids[$value['classes_id']] ?? '暂无'
                ];
            }
        }
        $e = new Export();
        $data = $e->downloadExcel($arr,$arrName,'筛选'.'-'.date('Y-m-d'),'Xlsx');
        return jsonReturn(1, $data);
    }

    /**
     * 批量设置分组
     * @param $data
     * @return Json
     */
    public function setAttrs($data):Json
    {
        $res = Student::whereIn('id',$data['id'])->update(['student_group_id'=>$data['student_group_id'].',']);
        if($res){
            return jsonReturn(1, []);
        }
        return jsonReturn(0, [],'保存失败');
    }

    /**
     * 导入学生设置分组
     * @param array $params
     * @return Json
     */
    public function importSetAttrs(array $params):Json
    {
        ini_set('memory_limit','4096M');
        set_time_limit(0);
        // 获取表单上传文件
        $file[] = request()->file('file');
        try {
            // 验证文件大小，名称等是否正确
            validate(['file' => 'filesize:5120000|fileExt:xls,xlsx'])
                ->check(['file'=>$file[0] ?? '']);
            $savename =\think\facade\Filesystem::disk('public')->putFile('upload', $file[0]);
            $fileExtendName = substr(strrchr($savename, '.'), 1);
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            $objReader->setReadDataOnly(TRUE);
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load(public_path().$savename);
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
                throw new \Exception('数据不能为空');
            }
            $model = new Student();
            $dataArr = [];
            for ($j = 2; $j <= $highestRow; $j++) {
                $dataArr[] = trim($objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue());
            }
            $arrs = [];
            if(!empty($dataArr)){
                foreach ($dataArr as $value){
                    if(!$model->where('username',$value)->count()){
                        $arrs[] = $value;
                    }
                }
            }
            if(!empty($arrs)){
                Log::error('数据库未出现的数据：');
                Log::error($arrs);
            }
            if(!empty($dataArr) && isset($params['student_group_id']) && $params['student_group_id']){
                $res = $model->whereIn('username',implode(',',$dataArr))->update(['student_group_id'=>$params['student_group_id'].',']);
                if(!$res){
                    throw new \Exception('设置分组失败');
                }
            }
            return jsonReturn(1, ['num'=>count($dataArr)]);
        } catch (\think\exception\ValidateException $e) {
            return jsonReturn(0, [],$e->getMessage());
        } catch (\Exception $e) {
            return jsonReturn(0, [],$e->getMessage());
        }
    }

    /**
     * 导入学生
     * @param array $params
     * @return Json
     */
    public function import(array $params):Json
    {
        ini_set('memory_limit','4096M');
        set_time_limit(0);
        // 获取表单上传文件
        $file[] = request()->file('file');
        try {
            // 验证文件大小，名称等是否正确
            validate(['file' => 'filesize:5120000|fileExt:xls,xlsx'])
                ->check(['file'=>$file[0] ?? '']);
            $savename =\think\facade\Filesystem::disk('public')->putFile('upload', $file[0]);
            $fileExtendName = substr(strrchr($savename, '.'), 1);
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            $objReader->setReadDataOnly(TRUE);
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load(public_path().$savename);
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
                throw new \Exception('数据不能为空');
            }
            //查询年级和班级
            $classes_list = Classes::where('is_del','=',0)->select()->toArray();
            $special_list = Special::where('is_del','=',0)->select()->toArray();
            $grades_list = Grades::where('is_del','=',0)->select()->toArray();
            $grades_ids = array_column($grades_list,'id','name');
            $special_ids = array_column($special_list,'id','name');
            $classes_ids = array_column($classes_list,'id','name');
            //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
            $sex_list = ['男'=>1,'女'=>2];
            $dataArr = [];
            for ($j = 2; $j <= 500; $j++) {
                $realname = trim($objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue());
                $username = trim($objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue());
                $sex = trim($objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue()) ?? '';
                $special = trim($objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue()) ?? '';
                $grades = trim($objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue()) ?? '';
                $classes = trim($objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue()) ?? '';
                //查询专业和年级
                $info = Special::where('is_del','=',0)->where('name','=',$special)->cache(true)->find();
                if($info){
                    //查询年级
                    $infos = Grades::where('is_del','=',0)
                        ->where('name','=',$grades)
                        ->where('special_id','=',$info->id)->cache(true)->find();
                    if($infos){
                        //查询班级
                        $infoss = Classes::where('is_del','=',0)
                            ->where('name','=',$classes)
                            ->where('grades_id','=',$infos->id)->cache(true)->find();
                        if($infoss && $username != '' && $realname != ''){
                            $safe_code = substr(md5(uniqid()), 5, 10);
                            $value = '123456' . $safe_code;
                            $password = password_hash($value, PASSWORD_DEFAULT);
                            $dataArr[] = [
                                'username'=>$username,
                                'realname'=>$realname,
                                'sex'=>$sex_list[$sex] ?? 0,
                                'create_user'=>WS_ADMIN_ID,
                                'special_id'=>$special_ids[$special],
                                'grades_id'=>$grades_ids[$grades],
                                'classes_id'=>$classes_ids[$classes],
                                'safe_code'=>$safe_code,
                                'password'=>$password,
                            ];
                        }
                    }
                }

            }
            $model = new Student();
            $res = $model->saveAll($dataArr);
            if(!$res){
                throw new \Exception('导入数据库失败');
            }
            return jsonReturn(1, ['num'=>count($dataArr)]);
        } catch (\think\exception\ValidateException $e) {
            return jsonReturn(0, [],$e->getMessage());
        }
    }


    /**
     * 导入专业年级和班级
     * @param array $params
     * @return Json
     */
    public function import3(array $params):Json
    {
        // 获取表单上传文件
        $file[] = request()->file('file');
        try {
            // 验证文件大小，名称等是否正确
            validate(['file' => 'filesize:512000|fileExt:xls,xlsx'])
                ->check(['file'=>$file[0] ?? '']);
            $savename =\think\facade\Filesystem::disk('public')->putFile('upload', $file[0]);
            $fileExtendName = substr(strrchr($savename, '.'), 1);
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            $objReader->setReadDataOnly(TRUE);
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load(public_path().$savename);
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
                throw new \Exception('数据不能为空');
            }
            //查询年级和班级
            $classes_list = Classes::where('is_del','=',0)->select()->toArray();
            $special_list = Special::where('is_del','=',0)->select()->toArray();
            $grades_list = Grades::where('is_del','=',0)->select()->toArray();
            $grades_ids = array_column($grades_list,'id','name');
            $special_ids = array_column($special_list,'id','name');
            $classes_ids = array_column($classes_list,'id','name');
            //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
            $dataArr = [];
            $dataArr2 = [];
            $dataArr2s = [];
            $dataArr3 = [];
            for ($j = 2; $j <= $highestRow; $j++) {
                $d = trim($objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue()) ?? '';
                $e = trim($objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue()) ?? '';
                $f = trim($objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue()) ?? '';

                if(isset($special_ids[$d])){


                    //判断是否存在这个数据
                    $info = Grades::where('is_del','=',0)
                        ->where('special_id','=',$special_ids[$d])->where('name','=',$e)->find();
                    if($info){
                        $info->toArray();
                        //处理班级
                        //查询这个专业存不存在
                        $infos = Classes::where('is_del','=',0)
                            ->where('grades_id','=',$info->id)->where('name','=',$f)->find();
                        if(!$infos){
                            $ds = [
                                'grades_id'=>$info->id,
                                'name'=>$f,
                                'is_del'=>0,
                                'sort'=>1,
                            ];
                            Db::name('classes')->insert($ds);
                        }
                    }
                    //处理年级
//                    if(!$info){
//                        $ds = [
//                            'special_id'=>$special_ids[$d],
//                            'name'=>$e,
//                            'is_del'=>0,
//                            'sort'=>1,
//                        ];
//                        Db::name('grades')->insert($ds);
//                    }

                }



            }
            dump($dataArr2s);die;


            die;
            //处理专业
            $dataArr = array_values(array_unique($dataArr));
            if(!empty($dataArr)){
                $arr = [];
                foreach ($dataArr as $value){
                    $arr[] = [
                        'name'=>$value,
                        'sort'=>0,
                        'is_del'=>0,
                        'level'=>1,
                        'pid'=>0,
                    ];
                }
                if(!empty($arr)){
                    Db::name('special')->insertAll($arr);
                }
            }
            return jsonReturn(1, ['num'=>count($dataArr)]);
        } catch (\think\exception\ValidateException $e) {
            return jsonReturn(0, [],$e->getMessage());
        }
    }

    /**
     * 导入test
     * @param array $params
     * @return Json
     */
    public function import2(array $params):Json
    {
        // 获取表单上传文件
        $file[] = request()->file('file');
        try {
            // 验证文件大小，名称等是否正确
            validate(['file' => 'filesize:51200|fileExt:xls,xlsx'])
                ->check(['file'=>$file[0] ?? '']);
            $savename =\think\facade\Filesystem::disk('public')->putFile('upload', $file[0]);
            $fileExtendName = substr(strrchr($savename, '.'), 1);
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            $objReader->setReadDataOnly(TRUE);
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load(public_path().$savename);
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;
            if ($lines <= 0) {
                throw new \Exception('数据不能为空');
            }
            $dataArr = [];
            $str = '';
            for ($j = 1; $j <= $highestRow; $j++) {
                $a = trim($objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue());
                $b = trim($objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue());
                $str .= "['bank_fullname'=>'{$b}', 'bank_name'=>'{$b}', 'code'=>'{$a}'],\r\n";
            }
            echo $str;die;
            return jsonReturn(1, ['num'=>count($dataArr)]);
        } catch (\think\exception\ValidateException $e) {
            return jsonReturn(0, [],$e->getMessage());
        }
    }

    /**
     * 详情
     * @param array $params
     * @return Json
     */
    public function read(array $params): Json
    {
        //数据验证
        try {
            validate(StudentValidate::class)->scene('details')->check($params);
        } catch (ValidateException $e) {
            // 验证失败 输出错误信息
            return jsonReturn(0, [], $e->getError());
        }
        $where = ['id'=>$params['id']];
        $data = Student::where($where)
            ->with(['special','grades','classes'])
            ->field('username,realname,sex,special_id,id,login_ip,login_time,created_at,updated_at,number,grades_id,classes_id,student_group_id')
            ->find();
        if($data) $data->toArray();
        $lists = [];
        $attr = StudentGroup::select()->toArray();
        if(!empty($attr)){
            $lists = array_column($attr,'name','id');
        }
        $attrs = !empty($data['student_group_id']) ? explode(',',$data['student_group_id']) : [];
        $str = [];
        if(!empty($attrs) && !empty($lists)){
            foreach ($attrs as $vv){
                if(isset($lists[$vv])) $str[] = $lists[$vv];
            }
        }
        $data['student_group_name'] = $str;
        return jsonReturn(1, $data);
    }

    /**
     * 保存班级
     * @param array $params
     * @return Json
     */
    public function save(array $params): Json
    {
        //数据验证
        try {
            validate(StudentValidate::class)->scene('add')->check($params);
        } catch (ValidateException $e) {
            // 验证失败 输出错误信息
            return jsonReturn(0, [], $e->getError());
        }

        if(isset($params['password']) && $params['password']){
            $params['safe_code'] = substr(md5(uniqid()), 5, 10);
            $value = $params['password'] . $params['safe_code'];
            $params['password'] = password_hash($value, PASSWORD_DEFAULT);
        }
        $data = Student::create([
            'username'=>$params['username'],
            'password'=>$params['password'],
            'realname'=>$params['realname'],
            'sex'=>$params['sex'],
            'student_group_id'=>$params['student_group_id'] ? $params['student_group_id'].',' : '',
            'status'=>$params['status'],
            'safe_code'=>$params['safe_code'],
            'classes_id'=>$params['classes_id'],
            'grades_id'=>$params['grades_id'],
            'special_id'=>$params['special_id']
        ]);
        if($data){
            return jsonReturn(1, []);
        }
        return jsonReturn(0, []);
    }

    /**
     * 编辑班级
     * @param array $params
     * @return Json
     */
    public function update(array $params): Json
    {
        //数据验证
        try {
            validate(StudentValidate::class)->scene('edit')->check($params);
        } catch (ValidateException $e) {
            // 验证失败 输出错误信息
            return jsonReturn(0, [], $e->getError());
        }
        $info = Student::findOne(['id'=>$params['id']]);
        if(!$info){
            return jsonReturn(0, [],'信息不存在');
        }
        $updateData = [
            'realname'=>$params['realname'],
            'sex'=>$params['sex'],
            'student_group_id'=>$params['student_group_id'] ? $params['student_group_id'].',' : '',
            'classes_id'=>$params['classes_id'],
            'grades_id'=>$params['grades_id'],
            'special_id'=>$params['special_id']
        ];
        if(isset($params['password']) && $params['password']){
            $value = $params['password'] . $info['safe_code'];
            $updateData['password'] = password_hash($value, PASSWORD_DEFAULT);
        }

        $data = Student::where('id','=',$params['id'])->update($updateData);
        if($data){
            return jsonReturn(1, []);
        }
        return jsonReturn(0, []);
    }

    /**
     * 编辑班级
     * @param array $params
     * @return Json
     */
    public function delete(array $params): Json
    {
        //数据验证
        try {
            validate(StudentValidate::class)->scene('del')->check($params);
        } catch (ValidateException $e) {
            // 验证失败 输出错误信息
            return jsonReturn(0, [], $e->getError());
        }
        $data = Student::where('id','=',$params['id'])->update(['is_del'=>1]);
        if($data){
            return jsonReturn(1, []);
        }
        return jsonReturn(0, []);
    }
}